﻿<cfsilent>
<cfsetting requesttimeout="300" />
<cfflush interval="1024" />
<cfscript>
	
		temFile = GetTempDirectory() & createUUID() & ".xls";
		workLoadUpdate = 0;
		workLoadInsert = 0;

		targetArgs = structNew();
		structInsert(targetArgs, "TabID", "labImport", true);

	</cfscript>
</cfsilent>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>导入课程工作量计算公式数据</title>
<link href="<cfoutput>#getProperty('cdnServer')#</cfoutput><cfoutput>#getContextRoot()#</cfoutput>/acadmicRes/assets/css/impoter.css" rel="stylesheet" type="text/css">
</head>

<body>
<cfif isDefined("FORM.upload")>
	
	<!--- 将文件保存至临时目录 --->
	<cftry>
		<cffile action="upload" fileField="upload" destination="#temFile#" nameconflict="overwrite">
		<cfcatch type="application">
			<p class="error">上传的文件类型不正确, 请使用电子表格作为数据导入文件.</p>
		</cfcatch>
	</cftry>
	
	<!--- 将文件转换为电子表格对象 --->
	<cfif fileExists(temFile)>
		<cftry>
			<cfspreadsheet action="read" src="#temFile#" excludeHeaderRow="true" headerrow="1" query="rs" sheetname="课程系数设置">
			<cfcatch type="application">
				<p class="error">电子表格文件格式不正确, 请使用下载的模板文件录入相关信息</p>
			</cfcatch>
		</cftry>
		
		<!--- 文件已成功转换为查询对象 --->
		<cfif isDefined("rs") and isQuery(rs)>
			<p class="success">尝试读取电子表格数据 ... 完成</p>
			
			<!--- 检查必要数据字段 --->
			<cfset columnChecked = true />
			<cfif not listFindNoCase(rs.columnList, "课程号")>
				<cfset columnChecked = false />
			</cfif>
			<cfif not listFindNoCase(rs.columnList, "双语系数")>
				<cfset columnChecked = false />
			</cfif>
			<cfif not listFindNoCase(rs.columnList, "实践系数")>
				<cfset columnChecked = false />
			</cfif>
			<cfif not listFindNoCase(rs.columnList, "主讲工作量权重量比")>
				<cfset columnChecked = false />
			</cfif>
			<cfif not listFindNoCase(rs.columnList, "第二主讲工作量权重量比")>
				<cfset columnChecked = false />
			</cfif>
			<cfif not listFindNoCase(rs.columnList, "助教工作量权重量比")>
				<cfset columnChecked = false />
			</cfif>
			<cfif not listFindNoCase(rs.columnList, "编制分类")>
				<cfset columnChecked = false />
			</cfif>
			<cfif not listFindNoCase(rs.columnList, "理论工作量计算公式")>
				<cfset columnChecked = false />
			</cfif>
			<cfif not listFindNoCase(rs.columnList, "实践工作量计算公式")>
				<cfset columnChecked = false />
			</cfif>
			<cfif not columnChecked>
				<p class="error">电子表格数据列不完整, 请检查模板文件是否包含以下必须字段信息: 课程号, 双语系数,实践系数, 主讲工作量权重量比, 第二主讲工作量权重量比, 助教工作量权重量比, 编制分类, 理论工作量计算公式, 实践工作量计算公式</p>
			</cfif>
			<cfif columnChecked>
				<p class="success">正在检查数据子项 ... 完成</p>
				<cftransaction>
				<cfloop query="rs">
					<cfset CourseCode = rs['课程号'][rs.currentRow] />
					<cfset LanguageWeight = rs['双语系数'][rs.currentRow] />
					<cfset PracticeWeight = rs['实践系数'][rs.currentRow] />
					<cfset FirstWeight = rs['主讲工作量权重量比'][rs.currentRow] />
					<cfset SecondWeight = rs['第二主讲工作量权重量比'][rs.currentRow] />
					<cfset LastWeight = rs['助教工作量权重量比'][rs.currentRow] />
					<cfset WorkLoadTypeName = rs['编制分类'][rs.currentRow] />
					<cfset TheoryFunctionName = rs['理论工作量计算公式'][rs.currentRow] />
					<cfset PracticeFunctionName = rs['实践工作量计算公式'][rs.currentRow] />
                    
                    <cfset sql = "SELECT a.cid
									FROM t_course a 
									WHERE a.course_code = :CourseCode " />
                                    
					<cfset queryObj = new query( datasource=application.dnsMaster )/>
                    <cfset queryObj.addParam( name="CourseCode", value=CourseCode, cfsqltype="cf_sql_varchar" )/>
                    <cfset rs_Course = queryObj.execute( sql=sql ).getResult()/>
                    
                    <cfset sql = "SELECT a.hrt
									FROM t_hr_workload a 
									WHERE a.type_name = :TypeName " />
                                    
					<cfset queryObj = new query( datasource=application.dnsMaster )/>
                    <cfset queryObj.addParam( name="TypeName", value=WorkLoadTypeName, cfsqltype="cf_sql_varchar" )/>
                    <cfset rs_WorkLoadType = queryObj.execute( sql=sql ).getResult()/>
                    
					<cfif isNull(TheoryFunctionName) or len(TheoryFunctionName) eq 0>
						<cfset TheoryFunctionName = "未设置" />
					</cfif>
					<cfif isNull(PracticeFunctionName) or len(PracticeFunctionName) eq 0>
						<cfset PracticeFunctionName = "未设置" />
					</cfif>
                    <cfset sql = "SELECT a.fun_id
									FROM t_workload_fun a 
									WHERE a.fun_name = :FunName " />
                                    
					<cfset queryObj = new query( datasource=application.dnsMaster )/>
                    <cfset queryObj.addParam( name="FunName", value=TheoryFunctionName, cfsqltype="cf_sql_varchar" )/>
                    <cfset rs_TheoryFunction = queryObj.execute( sql=sql ).getResult()/>
                    
                    <cfset sql = "SELECT a.fun_id
									FROM t_workload_fun a 
									WHERE a.fun_name = :FunName " />
                                    
					<cfset queryObj = new query( datasource=application.dnsMaster )/>
                    <cfset queryObj.addParam( name="FunName", value=PracticeFunctionName, cfsqltype="cf_sql_varchar" )/>
                    <cfset rs_PracticeFunction = queryObj.execute( sql=sql ).getResult()/>
                    
					<cfif rs_Course.recordCount EQ 0>
						<cfoutput>
							<p>对不起，不能找到课程号为：#CourseCode#的课程</p>
						</cfoutput>
						<cfelse>
						<cfif isNumeric(LanguageWeight)>
							<cfif isNumeric(PracticeWeight)>
								<cfif isNumeric(FirstWeight)>
									<cfif isNumeric(SecondWeight)>
										<cfif isNumeric(LastWeight)>
											<cfif rs_WorkLoadType.recordCount GT 0>
												<cfif rs_TheoryFunction.recordCount GT 0>
													<cfif rs_PracticeFunction.recordCount GT 0>
														<cfset TeacherWorkDivide = FirstWeight & ":" & SecondWeight & ":" & LastWeight />
                                                        <cfset sql = "SELECT c.cid,
																			   c.theory_fun,
																			   c.practice_fun,
																			   c.lang_weight,
																			   c.prac_weight,
																			   c.hrt,
																			   c.work_divide  
																		FROM t_course_workload c 
																		WHERE c.cid = :CourseID" />
																		
														<cfset queryObj = new query( datasource=application.dnsMaster )/>
														<cfset queryObj.addParam( name="CourseID", value=rs_Course.cid, cfsqltype="cf_sql_varchar" )/>
														<cfset rs_CourseWorkLoads = queryObj.execute( sql=sql ).getResult()/>
                    									
														<cfif rs_CourseWorkLoads.recordCount>
															<cfset arrayAppend(CourseWorkLoad,[rs_CourseWorkLoads.cid,rs_CourseWorkLoads.theory_fun,rs_CourseWorkLoads.practice_fun,rs_CourseWorkLoads.lang_weight,rs_CourseWorkLoads.prac_weight,rs_CourseWorkLoads.hrt,rs_CourseWorkLoads.work_divide]) />
														</cfif>
														<cfif isNull(CourseWorkLoad)>
                                                            <cfset sql = "INSERT INTO t_course_workload c (
																				   c.cid,
																				   c.theory_fun,
																				   c.practice_fun,
																				   c.lang_weight,
																				   c.prac_weight,
																				   c.hrt,
																				   c.work_divide 
																			)
																			VALUES(
																					:CourseId,
																					:TheoryFunction,
																					:PracticeFunction,
																					:LanguageWeight,
																					:PracticeWeight,
																					:WorkLoadType,
																					:TeacherWorkDivide 
																			) "/>
                                                            <cfset queryObj = new query( datasource=application.dnsMaster )/>
															<cfset queryObj.addParam( name="CourseId", value=rs_Course.cid, cfsqltype="cf_sql_varchar" )/>
                                                            <cfset queryObj.addParam( name="TheoryFunction", value=rs_TheoryFunction.fun_id, cfsqltype="cf_sql_char" )/>
                                                            <cfset queryObj.addParam( name="PracticeFunction", value=rs_PracticeFunction.fun_id, cfsqltype="cf_sql_char" )/>
                                                            <cfset queryObj.addParam( name="LanguageWeight", value=LanguageWeight, cfsqltype="cf_sql_decimal",scale = 3 )/>
                                                            <cfset queryObj.addParam( name="PracticeWeight", value=PracticeWeight, cfsqltype="cf_sql_decimal",scale = 3  )/>
                                                            <cfset queryObj.addParam( name="WorkLoadType", value=rs_WorkLoadType.hrt, cfsqltype="cf_sql_char" )/>
                                                            <cfset queryObj.addParam( name="TeacherWorkDivide", value=TeacherWorkDivide, cfsqltype="cf_sql_varchar" )/>
                                                            <cfset queryObj.execute( sql=sql )/>
															<cfset workLoadInsert ++ />
															<cfelse>
															<cfset workLoadUpdate ++ />
														</cfif>
														
                                                        	<cfset sql = "UPDATE  t_course_workload c
																			SET
																				   c.theory_fun = :TheoryFunction,
																				   c.practice_fun = :PracticeFunction,
																				   c.lang_weight = :LanguageWeight,
																				   c.prac_weight = :PracticeWeight,
																				   c.hrt = :WorkLoadType,
																				   c.work_divide = :TeacherWorkDivide 
																			WHERE c.cid = :CourseId "/>
                                                                            
                                                            <cfset queryObj = new query( datasource=application.dnsMaster )/>
															<cfset queryObj.addParam( name="CourseId", value=rs_Course.cid, cfsqltype="cf_sql_varchar" )/>
                                                            <cfset queryObj.addParam( name="TheoryFunction", value=rs_TheoryFunction.fun_id, cfsqltype="cf_sql_char" )/>
                                                            <cfset queryObj.addParam( name="PracticeFunction", value=rs_PracticeFunction.fun_id, cfsqltype="cf_sql_char" )/>
                                                            <cfset queryObj.addParam( name="LanguageWeight", value=LanguageWeight, cfsqltype="cf_sql_decimal",scale = 3 )/>
                                                            <cfset queryObj.addParam( name="PracticeWeight", value=PracticeWeight, cfsqltype="cf_sql_decimal",scale = 3 )/>
                                                            <cfset queryObj.addParam( name="WorkLoadType", value=rs_WorkLoadType.hrt, cfsqltype="cf_sql_char" )/>
                                                            <cfset queryObj.addParam( name="TeacherWorkDivide", value=TeacherWorkDivide, cfsqltype="cf_sql_varchar" )/>
                                                            <cfset queryObj.execute( sql=sql )/>
														<cfelse>
														<cfoutput>
															<p>对不起，课程号为：#CourseCode#的课程，找不到实践工作量计算公式</p>
														</cfoutput>
													</cfif>
													<cfelse>
													<cfoutput>
														<p>对不起，课程号为：#CourseCode#的课程，找不到理论工作量计算公式</p>
													</cfoutput>
												</cfif>
												<cfelse>
												<cfoutput>
													<p>对不起，课程号为：#CourseCode#的课程，找不到编制分类</p>
												</cfoutput>
											</cfif>
											<cfelse>
											<cfoutput>
												<p>对不起，课程号为：#CourseCode#的课程，助教权重系数不是数字</p>
											</cfoutput>
										</cfif>
										<cfelse>
										<cfoutput>
											<p>对不起，课程号为：#CourseCode#的课程，次讲权重系数不是数字</p>
										</cfoutput>
									</cfif>
									<cfelse>
									<cfoutput>
										<p>对不起，课程号为：#CourseCode#的课程，主讲权重系数不是数字</p>
									</cfoutput>
								</cfif>
								<cfelse>
								<cfoutput>
									<p>对不起，课程号为：#CourseCode#的课程，实践加权系数不是数字</p>
								</cfoutput>
							</cfif>
							<cfelse>
							<cfoutput>
								<p>对不起，课程号为：#CourseCode#的课程，双语加权系数不是数字</p>
							</cfoutput>
						</cfif>
					</cfif>
				</cfloop>
				</cftransaction>
			</cfif>
		</cfif>
	</cfif>
	<p>新增 [<cfoutput>#workLoadInsert#</cfoutput>], 更新 [<cfoutput>#workLoadUpdate#</cfoutput>].</p>
	<a target="_parent" href="<cfoutput>#buildURL('courseWorkLoad')#</cfoutput>">返回</a>

	<!--- 删除临时文件 --->
	<cfif fileExists(temFile)>
		<cffile action="delete" file="#temFile#" />
	</cfif>
</cfif>
</body>
</html>
